RipatDBA, SQL articles

Ripat DBA

Contact

Server viewer

Database

Download

Change menu

Extra tables

Examples

Q & A

License info

Version history

SQL articles

SQL articles.
Last changed: 04 May 2024

How to get the free disk space of a remote server?

I will discuss two methods to get the free disk space of a remote SQL server.

1. PowerShell
2. SQL job

1. PowerShell

Use a PowerShell file to collect the free diskspace. This method also requires the SqlServer module and DBATools to be installed.

Steps to do:
- create the database 'ServerViewer' and the table 'Drives', see Database and Extra tables
- create a new database with the name 'ServerViewerMaintenance'
- create a new table 'DrivesPSStaging' in the database 'ServerViewerMaintenance'
- create the PowerShell file get_diskspace_servername.ps1
- create a credential and a proxyaccount (SQLProxy)
- create a job with a jobstep that runs the PowerShell file with the proxyaccount

Create a table 'DrivesPSStaging'.

Create the PowerShell file get_diskspace_servername.ps1 and place the file in the folder D:\Apps\PS\.

# Server OSname, change this to the correct remote source server
$serverOS = 'remoteSQLserver'

# Server SQLname default instance, change this to the correct remote source server
$serverSQLDefaultInstance = 'remoteSQLserver'

########################################################################################################################

# Server SQLname to write data to, this will not change, this is our monitorserver
$serverSQL = 'YourMonitorServer'

# truncate the table
Invoke-SQLcmd -ServerInstance $serverSQL -Database "ServerViewerMaintenance" -Query 'TRUNCATE TABLE [dbo].[DrivesPSStaging]'

# fill the staging table
# write diskinfo from remote ComputerName $serverOS to SqlInstance $serverSQL into database ServerViewerMaintenance into table DrivesPSStaging
Get-DbaDiskSpace -ComputerName $serverOS | Select-Object ComputerName, Name, SizeInMB, FreeInMB, PercentFree | ConvertTo-DbaDataTable | Write-DbaDataTable -SqlInstance $serverSQL -Database ServerViewerMaintenance -Table DrivesPSStaging

# fill the destination table
# write the collected info from table DrivesPSStaging into [ServerViewer].[dbo].[Drives]
$insertQuery = "INSERT INTO [ServerViewer].[dbo].[Drives] (ServerName, Drive, TotalSize, FreeSpace, FreespaceTimestamp, PercentFree) SELECT '$serverSQLDefaultInstance' AS [ServerName], CAST([Name] AS CHAR(1)) AS [Drive], [SizeInMB] / 1.024 AS [TotalSize], [FreeInMB] / 1.024 AS [FreeSpace], [FreespaceTimestamp], [PercentFree] FROM [ServerViewerMaintenance].[dbo].[DrivesPSStaging]"
Invoke-SQLcmd -ServerInstance $serverSQL -Query $insertQuery

Create a job with a jobstep that runs the PowerShell file every day with a proxyaccount.

CmdExec
command=powershell.exe "D:\Apps\PS\get_diskspace_servername.ps1"
proxy_name=SQLProxy

2. SQL job

Use a SQL job that starts a stored procedure that gets the diskspace. This method requires a table and a stored procedure and a job on every remote server.

Steps to do:
- create the table 'Drives' in the master database on the remote server
- create the stored procedure 'sp_diskspace' in the master database on the remote server
- create a job '#dba - Drives' on the remote server
- retrieve the diskinfo from each remote server every day with i.e. a linked server and or via a SSIS package from your monitor server

Create the table 'Drives'.

Create the stored procedure 'sp_diskspace'.

-- run on remote server
USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_diskspace]
AS
SET NOCOUNT ON
DECLARE @hr INT
DECLARE @fso INT
DECLARE @drive CHAR(1)
DECLARE @odrive INT
DECLARE @TotalSize VARCHAR(20)
DECLARE @MB BIGINT

SET @MB = 1048576
INSERT [master].dbo.Drives(Drive, FreeSpace)
EXEC [master].dbo.xp_fixeddrives    -- shows the diskdrives
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT
IF @hr <> 0 
	BEGIN
		EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT
		IF @hr <> 0
			BEGIN
				EXEC sp_OAGetErrorInfo @fso
				UPDATE [master].dbo.Drives
				SET TotalSize = 0, FreeSpace = 0, ServerName = @@SERVERNAME, FreespaceTimestamp = (GETDATE())
				RETURN
			END
	END

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT Drive FROM [master].dbo.Drives
ORDER by Drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC @hr = sp_OAMethod @fso, 'GetDrive', @odrive OUT, @drive
		IF @hr <> 0 
			EXEC sp_OAGetErrorInfo @fso
		EXEC @hr = sp_OAGetProperty @odrive, 'TotalSize', @TotalSize OUT
		IF @hr <> 0 
			EXEC sp_OAGetErrorInfo @odrive
		UPDATE [master].dbo.Drives
		SET TotalSize = (@TotalSize / @MB) / 1.024, FreeSpace = FreeSpace / 1.024, ServerName = @@SERVERNAME, FreespaceTimestamp = (GETDATE())
		WHERE Drive = @drive
		FETCH NEXT FROM dcur INTO @drive
	END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr = sp_OADestroy @fso
IF @hr <> 0 
	EXEC sp_OAGetErrorInfo @fso
RETURN
GO
USE [master]
GO
EXEC sys.sp_addextendedproperty @name=N'Purpose', @value=N'Used for monitoring activities done by the DBA.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'sp_diskspace'
GO

Create the job '#dba - Drives'.

-- run on remote server
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'#dba - Drives', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Monitor the free disk space.', 
		@category_name=N'[Uncategorized (Local)]', 
		@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Drives', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'TRUNCATE TABLE master.dbo.Drives
GO
EXEC sp_configure ''show advanced options'' , 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ''Ole Automation Procedures'', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_diskspace
GO
EXEC sp_configure ''Ole Automation Procedures'', 0
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ''show advanced options'', 0
GO
RECONFIGURE WITH OVERRIDE
GO
', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Drives', 
		@enabled=1, 
		@freq_type=8, 
		@freq_interval=127, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20111010, 
		@active_end_date=99991231, 
		@active_start_time=70000, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
	IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Back to articles Next = How to check if the secondary node is readable in an AlwaysOn configuration?

Ripat DBA

Contact